<?php
class QWms
{	
	public function __construct($p_strCountry)
	{
		$this->_country	= $p_strCountry;
		$this->_db		= new Common_Db('wms_'.$this->_country);
	}
	
	function __destruct() 
	{
		$this->_db->closeConnection();
	}
	
	public function wms_info($p_arrSku)
	{
		$for_sale	= array();
		$in_transit	= array();
		
		/*if( $this->_country=='vi' )
		{
			$strQuery	= '
			SELECT items.sku_s `sku`
			FROM items 
			WHERE LOWER(items.sku_s) IN ("'.strtolower(implode('","', $p_arrSku)).'")  AND items.location_id IN (SELECT locations.id FROM locations WHERE (items_not_for_sale IS NULL OR items_not_for_sale = 0))
			';
			$for_sale	= $this->_db->fetchAll($strQuery, false);
			
			$strQuery	= '
				SELECT items.sku_s `sku`, 1 `quantity`
				FROM items 
				WHERE items.sku_s IN ("'.strtolower(implode('","', $p_arrSku)).'") AND items.location_id = "135111"
			';
			$in_transit	= $this->_db->fetchAll($strQuery);
		}
		else
		{ */
			$strQuery	= '
			SELECT p.sku, count(p.sku) as count
			FROM oms_live.wms_inventory i 
				Inner Join oms_live.ims_product p On i.fk_product = p.id_product 
				Inner Join oms_live.wms_inventory_status q on q.id_inventory_status = i.fk_inventory_status 
			WHERE q.id_inventory_status = 1 
				AND LOWER(p.sku) IN ("'.strtolower(implode('","', $p_arrSku)).'") 
				GROUP BY sku;
			';
			$for_sale	= $this->_db->fetchAll($strQuery, false);
			
			$strQuery	= '
				SELECT q.sku, sum(i.quantity) as quantity
				  FROM oms_live.ims_purchase_order_item  i 
				  Inner Join oms_live.ims_supplier_product p On 
				  i.fk_supplier_product = p.id_supplier_product 
				  Inner Join oms_live.ims_product q 
				  On p.fk_product = q.id_product
				  Inner join oms_live.ims_purchase_order r
				on i.fk_purchase_order = r.id_purchase_order
				  Inner join oms_live.ims_purchase_order_status s
				on r.fk_purchase_order_status = s.id_purchase_order_status
				  WHERE i.fk_purchase_order_item_status=2
				  and r.fk_purchase_order_status In ("1","3","6","5")
				  AND i.fk_sales_order_item  is null
				  AND LOWER(q.sku) IN ("'.strtolower(implode('","', $p_arrSku)).'")
				GROUP BY q.sku;
			';
			$in_transit	= $this->_db->fetchAll($strQuery);
		/*} */
		
		return array('for_sale'=>$for_sale, 'in_transit'=>$in_transit);
	}
  
	public function CrontabWMSQuery()
  {
		$strQuery	= '	SELECT * FROM ims_sales_order_item i 
						inner join ims_sales_order_item_status k on 
						 i.fk_sales_order_item_status = k.id_sales_order_item_status
						inner join ims_sales_order l on
						 i.fk_sales_order = l.id_Sales_order  
						 where i.created_at > DATE_SUB(CURDATE(), INTERVAL 15 DAY);'; 				
        $arrData = $this->_db->fetchAll($strQuery); 	          
	    return  $arrData;                 
  }

	public function dailyReport($p_CountryName, $p_CountryId)
	{
		$arrDataReturn = array();
		$now = getdate();
	    $today = date('Y-m-d H:i:s', mktime($now['hours'], $now['minutes'], $now["seconds"], $now['mon'], $now['mday'], $now['year']));
		$datefilename = date('dmY', mktime($now['hours'], $now['minutes'], $now["seconds"], $now['mon'], $now['mday'], $now['year']));
		$arrArray = array(
			'category' => 'Date', 
			'key' => $today,
			'description' => "", 
			'Item' => $p_CountryName,
			'Order' => ""					
			); 	
		
		array_push($arrDataReturn, 	$arrArray);
		
		$arrArray = array(
			'category' => 'Day', 
			'key' => date('l', strtotime($today)),
			'description' => "", 
			'Item' => "Items",
			'Order' => "Orders"					
			); 	
		
		// Change value foreach country m.fk_queue not in
		$arrArrayfk_queue = array(
				'th'  => '15',
				'id'  => '16',
				'ma'  => '31',
				'ph'  => '0'
		);
		
		array_push($arrDataReturn, 	$arrArray);
				
		$arrItemQuery = array(
			'Volumes'			=>'SELECT count(i.created_at)  as count FROM ims_sales_order_item i 
					inner join ims_sales_order_item_status k on 
					 i.fk_sales_order_item_status = k.id_sales_order_item_status
					inner join ims_sales_order l on
					 i.fk_sales_order = l.id_Sales_order
					where  i.created_at > CASE WHEN DAYOFWEEK(NOW()) = 2 Then 
													Date_Sub(Now(),  interval 48 hour)
													 ELSE
													 Date_Sub(Now(),  interval 24 hour) End;',
														
			'Total'					=> ' SELECT count(i.created_at)  as count FROM ims_sales_order_item i 
										  inner join ims_sales_order_item_status k on 
										   i.fk_sales_order_item_status = k.id_sales_order_item_status
										  inner join ims_sales_order l on
										   i.fk_sales_order = l.id_Sales_order
										  where  i.fk_sales_order_item_status = 4;',
			'Credit_Card'			=> " SELECT count(i.created_at)  as count FROM ims_sales_order_item i 
										   inner join ims_sales_order_item_status k on 
										   i.fk_sales_order_item_status = k.id_sales_order_item_status
										   inner join ims_sales_order l on i.fk_sales_order = l.id_Sales_order
										   where  l.payment_method In ('Adyen_CreditCard', 'DragonPay' , 'BDO_OnlineInstallment', 'NoPayment', 'IPay88')
										  and i.fk_sales_order_item_status = 4 ;" ,
			'Bank_Transfers'		=> " SELECT count(i.created_at)  as count FROM ims_sales_order_item i 
										  inner join ims_sales_order_item_status k on 
										   i.fk_sales_order_item_status = k.id_sales_order_item_status
										  inner join ims_sales_order l on i.fk_sales_order = l.id_Sales_order
										  where  l.payment_method = 'BankTransfer'
										  and i.fk_sales_order_item_status = 4 ;" ,			
			'COD'					=> " SELECT count(i.created_at)  as count FROM ims_sales_order_item i 
										 inner join ims_sales_order_item_status k on 
										  i.fk_sales_order_item_status = k.id_sales_order_item_status
										 inner join ims_sales_order l on
										  i.fk_sales_order = l.id_Sales_order
										 where  l.payment_method = ('CashOnDelivery')  and i.fk_sales_order_item_status = 4;",
			'OTC'					=> "SELECT count(i.created_at)  as count FROM ims_sales_order_item i 
										inner join ims_sales_order_item_status k on 
										 i.fk_sales_order_item_status = k.id_sales_order_item_status
										inner join ims_sales_order l on
										 i.fk_sales_order = l.id_Sales_order
										where l.payment_method In ('Payment2C2P_OVERTHECOUNTER', 'SevenEleven')   and
										i.fk_sales_order_item_status = 4 ;",
						
			'Total_Crossdocking'			=> " SELECT count(l.fk_Sales_order) as count FROM ims_purchase_order_item i
									inner join ims_purchase_order k on i.fk_purchase_order = k.id_purchase_order
									inner join ims_sales_order_item l on i.fk_sales_order_item = l.id_sales_order_item 
									where k.po_type = 'Demand'
									and k.fk_purchase_order_status in ('1')",			
			'Cross_Docking_Items_More_48_hrs'=>" SELECT count(l.fk_Sales_order) as count FROM ims_purchase_order_item i
									inner join ims_purchase_order k on i.fk_purchase_order = k.id_purchase_order
									inner join ims_sales_order_item l on i.fk_sales_order_item = l.id_sales_order_item 
									where k.po_type = 'Demand'
									and k.fk_purchase_order_status in ('1')
									and k.created_at < Date_Sub(sysdate(),interval 2 day);",
			'Total_Cross_Docking_PO'		=> " SELECT count(i.po_number) as count FROM oms_live.ims_purchase_order i
									where i.po_type = 'Demand' and i.fk_purchase_order_status in ('1');",
			'Cross_Docking_POs_More_48_hrs' => "SELECT count(i.po_number) as count FROM oms_live.ims_purchase_order i
										where i.po_type = 'Demand'
										and i.fk_purchase_order_status in ('1') 
										and i.created_at < Date_Sub(sysdate(),interval 2 day);",
			'Sourcing_Pending'				=> "",
			
			
			'Picking_pending'				=> "SELECT count(i.created_at)  as count FROM ims_sales_order_item i 
						inner join ims_sales_order_item_status k on 
						 i.fk_sales_order_item_status = k.id_sales_order_item_status
						inner join ims_sales_order l on
						 i.fk_sales_order = l.id_Sales_order
						where  i.fk_sales_order_item_status = '53' ;",	
			'Packing_pending'				=> "SELECT count(i.created_at)  as count FROM ims_sales_order_item i 
						inner join ims_sales_order_item_status k on 
						 i.fk_sales_order_item_status = k.id_sales_order_item_status
						inner join ims_sales_order l on
						 i.fk_sales_order = l.id_Sales_order
						where  i.fk_sales_order_item_status = '49'  ;",
						
			'Total_outbound_pending'		=> "SELECT count(i.fk_sales_order_item_status) as count  FROM ims_sales_order_item i 
												  inner join ims_sales_order_item_status k on 
												   i.fk_sales_order_item_status = k.id_sales_order_item_status
												  inner join ims_sales_order l on
												   i.fk_sales_order = l.id_Sales_order
											left join oms_queue_sales_order_item m on i.id_sales_order_item = m.fk_Sales_order_item 
											where  i.fk_sales_order_item_status in('4','15','23','24','25','26','28','30','31','32',
												  '33','34','35','36','40','41','42','43','47','48','49','50','51','52','53',
												  '54','58','67','69','70','71','73') ;",
			'Total_pending_excluding_otc' => "SELECT count(i.fk_sales_order_item_status) as count FROM ims_sales_order_item i 
											  inner join ims_sales_order_item_status k on 
											   i.fk_sales_order_item_status = k.id_sales_order_item_status
											  inner join ims_sales_order l on
											   i.fk_sales_order = l.id_Sales_order
										left join oms_queue_sales_order_item m on
										 i.id_sales_order_item = m.fk_Sales_order_item 
										where  i.fk_sales_order_item_status in('4','15','23','24','25','26','28','30','31','32',
											  '33','34','35','36','40','41','42','43','47','48','49','50','51','52','53',
											  '54','58','67','69','70','71','73')  
										and i.fk_sales_order_item_status Not In ('4','69')
										and l.payment_method not in ('Payment2C2P_OVERTHECOUNTER','SevenEleven','BankTransfer')
										and m.fk_queue not in ('" . $arrArrayfk_queue[$p_CountryId] ."')",
											
			'Pending_0-2_days'				=> "SELECT count(i.fk_sales_order_item_status) as count  FROM ims_sales_order_item i 
											  inner join ims_sales_order_item_status k on 
											   i.fk_sales_order_item_status = k.id_sales_order_item_status
											  inner join ims_sales_order l on
											   i.fk_sales_order = l.id_Sales_order
										left join oms_queue_sales_order_item m on
										 i.id_sales_order_item = m.fk_Sales_order_item 
										where  i.fk_sales_order_item_status in('4','15','23','24','25','26','28','30','31','32',
											  '33','34','35','36','40','41','42','43','47','48','49','50','51','52','53',
											  '54','58','67','69','70','71','73')  
										and i.fk_sales_order_item_status Not In ('4','69')
										and l.payment_method not in ('Payment2C2P_OVERTHECOUNTER','SevenEleven','BankTransfer')
										and m.fk_queue not in ('" . $arrArrayfk_queue[$p_CountryId] ."')
										and i.created_at > Date_Sub(sysdate(), Interval 2 day);",
			'Pending_2-4_days'				=>"SELECT count(i.fk_sales_order_item_status) as count  FROM ims_sales_order_item i 
											  inner join ims_sales_order_item_status k on 
											   i.fk_sales_order_item_status = k.id_sales_order_item_status
											  inner join ims_sales_order l on
											   i.fk_sales_order = l.id_Sales_order
										left join oms_queue_sales_order_item m on
										 i.id_sales_order_item = m.fk_Sales_order_item 
										where  i.fk_sales_order_item_status in('4','15','23','24','25','26','28','30','31','32',
											  '33','34','35','36','40','41','42','43','47','48','49','50','51','52','53',
											  '54','58','67','69','70','71','73')  
										and i.fk_sales_order_item_status Not In ('4','69')
										and l.payment_method not in ('Payment2C2P_OVERTHECOUNTER','SevenEleven','BankTransfer')
										and m.fk_queue not in ('" . $arrArrayfk_queue[$p_CountryId] ."')
										and i.created_at between Date_Sub(sysdate(),interval 4 day) 
										and Date_Sub(sysdate(),interval 2 day);",
			'Pending_4-7_days'				=> "SELECT count(i.fk_sales_order_item_status) as count FROM ims_sales_order_item i 
											  inner join ims_sales_order_item_status k on 
											   i.fk_sales_order_item_status = k.id_sales_order_item_status
											  inner join ims_sales_order l on
											   i.fk_sales_order = l.id_Sales_order
										left join oms_queue_sales_order_item m on
										 i.id_sales_order_item = m.fk_Sales_order_item 
										where  i.fk_sales_order_item_status in('4','15','23','24','25','26','28','30','31','32',
											  '33','34','35','36','40','41','42','43','47','48','49','50','51','52','53',
											  '54','58','67','69','70','71','73')  
										and i.fk_sales_order_item_status Not In ('4','69')
										and l.payment_method not in ('Payment2C2P_OVERTHECOUNTER','SevenEleven','BankTransfer')
										and m.fk_queue not in ('" . $arrArrayfk_queue[$p_CountryId] . "')
						and i.created_at between Date_Sub(sysdate(),interval 7 day) and Date_Sub(sysdate(),interval 4 day);",
			'Pending_more_7_days'			=> 	"SELECT count(i.fk_sales_order_item_status)  as count FROM ims_sales_order_item i 
											  inner join ims_sales_order_item_status k on 
											   i.fk_sales_order_item_status = k.id_sales_order_item_status
											  inner join ims_sales_order l on
											   i.fk_sales_order = l.id_Sales_order
										left join oms_queue_sales_order_item m on
										 i.id_sales_order_item = m.fk_Sales_order_item 
										where  i.fk_sales_order_item_status in('4','15','23','24','25','26','28','30','31','32',
											  '33','34','35','36','40','41','42','43','47','48','49','50','51','52','53',
											  '54','58','67','69','70','71','73')  
										and i.fk_sales_order_item_status Not In ('4','69')
										and l.payment_method not in ('Payment2C2P_OVERTHECOUNTER','SevenEleven','BankTransfer')
										and m.fk_queue not in ('" . $arrArrayfk_queue[$p_CountryId] ."')
								and i.created_at < Date_Sub(sysdate(),interval 7 day) ;",
								
			'Customer_Pending_0-2_Days'		=>  "Select count(i.id_sales_order_item) as count from oms_live.ims_sales_order_item i
												Where i.fk_sales_order_item_status = '69' 
												    and i.created_at > Date_Sub(sysdate(), Interval 2 day);",
			'Customer_Pending_2-4_Days'		=>	"Select count(i.id_sales_order_item) as count from oms_live.ims_sales_order_item i
												Where i.fk_sales_order_item_status = '69' 
												    and i.created_at between Date_Sub(sysdate(),interval 4 day) 
													and Date_Sub(sysdate(),interval 2 day);",
			'Customer_Pending_4-7_Days'		=>	"Select count(i.id_sales_order_item) as count from oms_live.ims_sales_order_item i
												Where i.fk_sales_order_item_status = '69' 
												   and i.created_at between Date_Sub(sysdate(),interval 7 day) 
												   and Date_Sub(sysdate(),interval 4 day);",
			'Customer_Pending_more_7_Days'	=>  "Select count(i.id_sales_order_item) as count from oms_live.ims_sales_order_item i
												Where i.fk_sales_order_item_status = '69' 
												   and i.created_at < Date_Sub(sysdate(),interval 7 day);"		
		);
	
		$arrOrderQuery = array(
			'Volumes'			=>"SELECT count(distinct i.fk_sales_order)  as count FROM ims_sales_order_item i 
						inner join ims_sales_order_item_status k on 
						 i.fk_sales_order_item_status = k.id_sales_order_item_status
						inner join ims_sales_order l on
						 i.fk_sales_order = l.id_Sales_order
						where  i.created_at > CASE WHEN DAYOFWEEK(NOW()) = 2 Then 
													Date_Sub(Now(),  interval 48 hour)
													 ELSE
													 Date_Sub(Now(),  interval 24 hour)
											End;",	
			
			'Total'				=> "SELECT count(distinct i.fk_Sales_order)  as count FROM ims_sales_order_item i 
						inner join ims_sales_order_item_status k on 
						 i.fk_sales_order_item_status = k.id_sales_order_item_status
						inner join ims_sales_order l on
						 i.fk_sales_order = l.id_Sales_order
						where  i.fk_sales_order_item_status = '4'  ;",
			'Credit_Card'		=>"SELECT count(distinct i.fk_Sales_order)  as count FROM ims_sales_order_item i 
								  inner join ims_sales_order_item_status k on 
								  i.fk_sales_order_item_status = k.id_sales_order_item_status
								  inner join ims_sales_order l on i.fk_sales_order = l.id_Sales_order
								  where  l.payment_method In ('Adyen_CreditCard', 'DragonPay' , 'BDO_OnlineInstallment', 'NoPayment', 'IPay88')  
									and i.fk_sales_order_item_status = '4'  ;",
			'Bank_Transfers'	=>"SELECT count(distinct i.fk_Sales_order)  as count FROM ims_sales_order_item i 
									   inner join ims_sales_order_item_status k on 
										i.fk_sales_order_item_status = k.id_sales_order_item_status
										inner join ims_sales_order l on i.fk_sales_order = l.id_Sales_order
										where  l.payment_method = 'BankTransfer'
										and i.fk_sales_order_item_status = '4'  ;",			
			'COD'				=>"SELECT count(distinct i.fk_sales_order) as count FROM ims_sales_order_item i 
						inner join ims_sales_order_item_status k on 
						 i.fk_sales_order_item_status = k.id_sales_order_item_status
						inner join ims_sales_order l on
						 i.fk_sales_order = l.id_Sales_order
						where l.payment_method = ('CashOnDelivery')    and
						i.fk_sales_order_item_status = '4'  ;",
			'OTC'				=> "SELECT count(distinct i.fk_sales_order) as count  FROM ims_sales_order_item i 
						inner join ims_sales_order_item_status k on 
						 i.fk_sales_order_item_status = k.id_sales_order_item_status
						inner join ims_sales_order l on
						 i.fk_sales_order = l.id_Sales_order
						where  l.payment_method In  ('Payment2C2P_OVERTHECOUNTER', 'SevenEleven')  and
						i.fk_sales_order_item_status = '4'   ;",
			
			'Total_Crossdocking'				=> " SELECT count(distinct l.fk_Sales_order) as count FROM ims_purchase_order_item i
									inner join ims_purchase_order k on i.fk_purchase_order = k.id_purchase_order
									inner join ims_sales_order_item l on i.fk_sales_order_item = l.id_sales_order_item 
									where k.po_type = 'Demand'
									and k.fk_purchase_order_status in ('1');",
			'Cross_Docking_Items_More_48_hrs'	=>" SELECT count(distinct l.fk_Sales_order) as count FROM ims_purchase_order_item i
									inner join ims_purchase_order k on i.fk_purchase_order = k.id_purchase_order
									inner join ims_sales_order_item l on i.fk_sales_order_item = l.id_sales_order_item 
									where k.po_type = 'Demand'
									and k.fk_purchase_order_status in ('1')
									and k.created_at < Date_Sub(sysdate(),interval 2 day);",
			'Total_Cross_Docking_PO'			=> "",
			'Cross_Docking_POs_More_48_hrs'		=> "",
			'Sourcing_Pending'					=> "",
			
			'Picking_pending'				=> "SELECT count(distinct i.fk_sales_order) as count  FROM ims_sales_order_item i 
						inner join ims_sales_order_item_status k on 
						 i.fk_sales_order_item_status = k.id_sales_order_item_status
						inner join ims_sales_order l on
						 i.fk_sales_order = l.id_Sales_order
						where  i.fk_sales_order_item_status = '53'  ;",
			'Packing_pending'				=> "SELECT count(distinct i.fk_sales_order) as count FROM ims_sales_order_item i 
						inner join ims_sales_order_item_status k on 
						 i.fk_sales_order_item_status = k.id_sales_order_item_status
						inner join ims_sales_order l on
						 i.fk_sales_order = l.id_Sales_order
						where i.fk_sales_order_item_status = '49'  ;",
		
			'Total_outbound_pending'		=> "SELECT count(distinct i.fk_sales_order) as count  FROM ims_sales_order_item i 
												  inner join ims_sales_order_item_status k on 
												   i.fk_sales_order_item_status = k.id_sales_order_item_status
												  inner join ims_sales_order l on
												   i.fk_sales_order = l.id_Sales_order
											left join oms_queue_sales_order_item m on i.id_sales_order_item = m.fk_Sales_order_item 
											where  i.fk_sales_order_item_status in('4','15','23','24','25','26','28','30','31','32',
												  '33','34','35','36','40','41','42','43','47','48','49','50','51','52','53',
												  '54','58','67','69','70','71','73') ;",
												
			'Total_pending_excluding_otc' => "SELECT count(distinct i.fk_sales_order) as count FROM ims_sales_order_item i 
											  inner join ims_sales_order_item_status k on 
											   i.fk_sales_order_item_status = k.id_sales_order_item_status
											  inner join ims_sales_order l on
											   i.fk_sales_order = l.id_Sales_order
										left join oms_queue_sales_order_item m on
										 i.id_sales_order_item = m.fk_Sales_order_item 
										where  i.fk_sales_order_item_status in('4','15','23','24','25','26','28','30','31','32',
											  '33','34','35','36','40','41','42','43','47','48','49','50','51','52','53',
											  '54','58','67','69','70','71','73')  
										and i.fk_sales_order_item_status Not In ('4','69')
										and l.payment_method not in ('Payment2C2P_OVERTHECOUNTER','SevenEleven','BankTransfer')
										and m.fk_queue not in ('" . $arrArrayfk_queue[$p_CountryId] ."')",
											
			'Pending_0-2_days'				=> "SELECT count(distinct i.fk_sales_order) as count  FROM ims_sales_order_item i 
											  inner join ims_sales_order_item_status k on 
											   i.fk_sales_order_item_status = k.id_sales_order_item_status
											  inner join ims_sales_order l on
											   i.fk_sales_order = l.id_Sales_order
										left join oms_queue_sales_order_item m on
										 i.id_sales_order_item = m.fk_Sales_order_item 
										where  i.fk_sales_order_item_status in('4','15','23','24','25','26','28','30','31','32',
											  '33','34','35','36','40','41','42','43','47','48','49','50','51','52','53',
											  '54','58','67','69','70','71','73')  
										and i.fk_sales_order_item_status Not In ('4','69')
										and l.payment_method not in ('Payment2C2P_OVERTHECOUNTER','SevenEleven','BankTransfer')
										and m.fk_queue not in ('" . $arrArrayfk_queue[$p_CountryId] ."')
										and i.created_at > Date_Sub(sysdate(), Interval 2 day);",
			'Pending_2-4_days'				=>"SELECT count(distinct i.fk_sales_order) as count  FROM ims_sales_order_item i 
											  inner join ims_sales_order_item_status k on 
											   i.fk_sales_order_item_status = k.id_sales_order_item_status
											  inner join ims_sales_order l on
											   i.fk_sales_order = l.id_Sales_order
										left join oms_queue_sales_order_item m on
										 i.id_sales_order_item = m.fk_Sales_order_item 
										where  i.fk_sales_order_item_status in('4','15','23','24','25','26','28','30','31','32',
											  '33','34','35','36','40','41','42','43','47','48','49','50','51','52','53',
											  '54','58','67','69','70','71','73')  
										and i.fk_sales_order_item_status Not In ('4','69')
										and l.payment_method not in ('Payment2C2P_OVERTHECOUNTER','SevenEleven','BankTransfer')
										and m.fk_queue not in ('" . $arrArrayfk_queue[$p_CountryId] ."')
										and i.created_at between Date_Sub(sysdate(),interval 4 day) 
										and Date_Sub(sysdate(),interval 2 day);",
			'Pending_4-7_days'				=> "SELECT count(distinct i.fk_sales_order) as count FROM ims_sales_order_item i 
											  inner join ims_sales_order_item_status k on 
											   i.fk_sales_order_item_status = k.id_sales_order_item_status
											  inner join ims_sales_order l on
											   i.fk_sales_order = l.id_Sales_order
										left join oms_queue_sales_order_item m on
										 i.id_sales_order_item = m.fk_Sales_order_item 
										where  i.fk_sales_order_item_status in('4','15','23','24','25','26','28','30','31','32',
											  '33','34','35','36','40','41','42','43','47','48','49','50','51','52','53',
											  '54','58','67','69','70','71','73')  
										and i.fk_sales_order_item_status Not In ('4','69')
										and l.payment_method not in ('Payment2C2P_OVERTHECOUNTER','SevenEleven','BankTransfer')
										and m.fk_queue not in ('" . $arrArrayfk_queue[$p_CountryId] . "')
										and i.created_at between Date_Sub(sysdate(),interval 7 day) 
										and Date_Sub(sysdate(),interval 4 day);",
			'Pending_more_7_days'			=> 	"SELECT count(distinct i.fk_sales_order)  as count FROM ims_sales_order_item i 
											  inner join ims_sales_order_item_status k on 
											   i.fk_sales_order_item_status = k.id_sales_order_item_status
											  inner join ims_sales_order l on
											   i.fk_sales_order = l.id_Sales_order
										left join oms_queue_sales_order_item m on
										 i.id_sales_order_item = m.fk_Sales_order_item 
										where  i.fk_sales_order_item_status in('4','15','23','24','25','26','28','30','31','32',
											  '33','34','35','36','40','41','42','43','47','48','49','50','51','52','53',
											  '54','58','67','69','70','71','73')  
										and i.fk_sales_order_item_status Not In ('4','69')
										and l.payment_method not in ('Payment2C2P_OVERTHECOUNTER','SevenEleven','BankTransfer')
										and m.fk_queue not in ('" . $arrArrayfk_queue[$p_CountryId] ."')
								and i.created_at < Date_Sub(sysdate(),interval 7 day) ;",
						
			'Customer_Pending_0-2_Days'		=>  "Select count(distinct i.id_sales_order_item) as count from oms_live.ims_sales_order_item i
												Where i.fk_sales_order_item_status = '69' 
												    and i.created_at > Date_Sub(sysdate(), Interval 2 day);",
			'Customer_Pending_2-4_Days'		=>	"Select count(distinct i.id_sales_order_item) as count from oms_live.ims_sales_order_item i
												Where i.fk_sales_order_item_status = '69' 
												    and i.created_at between Date_Sub(sysdate(),interval 4 day) 
													and Date_Sub(sysdate(),interval 2 day);",
			'Customer_Pending_4-7_Days'		=>	"Select count(distinct i.id_sales_order_item) as count from oms_live.ims_sales_order_item i
												Where i.fk_sales_order_item_status = '69' 
												   and i.created_at between Date_Sub(sysdate(),interval 7 day) 
												   and Date_Sub(sysdate(),interval 4 day);",
			'Customer_Pending_more_7_Days'	=>  "Select count(distinct i.id_sales_order_item) as count from oms_live.ims_sales_order_item i
												Where i.fk_sales_order_item_status = '69' 
												   and i.created_at < Date_Sub(sysdate(),interval 7 day);"		
			);
		
		$arrDescription = 	array(
			'Volumes'			=>"Orders since last day of Operations 6pm (for Monday will include Sunday if the WH is opened on Saturday)",	
			'Total'				=> "Total number of items in the queue",
			'Credit_Card'		=>"Credit Card",
			'Bank_Transfers'	=> 'Bank Transfers',
			'COD'				=>"Number of items in the queue",
			'OTC'				=> "Number of items in the queue",
			
			'Total_Crossdocking'					=> "Total Crossdocking",
			'Cross_Docking_Items_More_48_hrs'		=>"Cross Docking Items More 48 hrs",
			'Total_Cross_Docking_PO'				=> "Total Cross Docking PO",
			'Cross_Docking_POs_More_48_hrs'			=> "Cross Docking POs More 48 hrs",
			'Sourcing_Pending'						=> 'Sourcing Pending',
			
			'Picking_pending'				=> "Picking queue at the end of the day",
			'Packing_pending'				=> "Packing queue at the end of the day",
			
			'Total_outbound_pending'		=> "Total Outbound pending",
			'Total_pending_excluding_otc'	=> "Total Outbound pending at end of the day (excluding otc orders pending payment verification)",
			'Pending_0-2_days'				=>"Outbound Pending for orders placed between 0 and 2 days",
			'Pending_2-4_days'				=> "Outbound Pending for orders placed between 2 and 4 days",
			'Pending_4-7_days'				=>"Outbound Pending for orders placed between 4 and 7 days",
			'Pending_more_7_days'			=> "Outbound Pending for orders placed > 7 Days",
			
			'Customer_Pending_0-2_Days'		=>  "Customer Service Pending 0 and 2 Days",
			'Customer_Pending_2-4_Days'		=>	"Customer Service Pending 2 and 4 Days",
			'Customer_Pending_4-7_Days'		=>	"Customer Service Pending 4 and 7 Days",
			'Customer_Pending_more_7_Days'	=>  "Customer Service Pending > 7 Days"
			);
		
		$arrCateogry = 	array(
			'Volumes'						=>"General",	
			
			'Total'							=> "Payments",
			'Credit_Card'					=>"",
			'Bank_Transfers'				=>"",
			'COD'							=>"",
			'OTC'							=> "",
						
			'Total_Crossdocking'				=> "Sourcing",
			'Cross_Docking_Items_More_48_hrs'	=>"",
			'Total_Cross_Docking_PO'			=> "",
			'Cross_Docking_POs_More_48_hrs'		=> "",
			'Sourcing_Pending'					=> '',
			
			'Picking_pending'				=> "Warehouse",
			'Packing_pending'				=> "",
			
			'Total_outbound_pending'		=> "Delivery / Outbound",
			'Total_pending_excluding_otc'	=> "",
			'Pending_0-2_days'				=>"",
			'Pending_2-4_days'				=> "",
			'Pending_4-7_days'				=>"",
			'Pending_more_7_days'			=> "",
			
			'Customer_Pending_0-2_Days'		=> "Customer Service",
			'Customer_Pending_2-4_Days'			=>	"" ,
			'Customer_Pending_4-7_Days'	=>"",
			'Customer_Pending_more_7_Days'		=> ""
			);
		
		$arrDataOrder =array();
		
		foreach ($arrItemQuery as $key => $item1)
		{
			$key1 = $key;
			$value1 = $arrItemQuery[$key1];
			
			$arrData1 = null;
			if($value1)
			{			
				$arrData1 = $this->_db->fetchAll($value1); 	            
			}
			
			//get data order
			$arrOrderItem =  $this->viewOrder($p_CountryId, $key1);
			
			if($arrOrderItem !=null)
			{
				$arrDataOrder[$p_CountryId . ".orderlist_" . $key1 .'_'. $datefilename] = $arrOrderItem;
			}
			
			$key2 = $key;
			$value2 = $arrOrderQuery[$key2];
			
			$arrData2 = null;
			if($value2)
			{			
				$arrData2 = $this->_db->fetchAll($value2); 	            
			} 
			
			$arrArray = array(
				'category' => $arrCateogry[$key], 
				'key' => $key,
				'description' => $arrDescription[$key], 
				'Item' => $arrData1[0]['count'],
				'Order' => $arrData2[0]['count']					
				); 				
			array_push($arrDataReturn, 	$arrArray);
		}
		$arrResult["maindata"] =$arrDataReturn;
		$arrResult["orderdata"] =$arrDataOrder;
		return $arrResult;
	}
	
	public function viewOrder($p_CountryId, $idQuery)
	{
	    // Change value foreach country m.fk_queue not in
		$arrArrayfk_queue = array(
				'th'  => '15',
				'id'  => '16',
				'ma'  => '31',
				'ph'  => '0'
		);
						
		$arrItemQuery = array(
		/*	'Total_pending_excluding_otc' => "SELECT l.order_nr,l.created_at,l.payment_method,k.name,k.desc FROM ims_sales_order_item i 
											  inner join ims_sales_order_item_status k on 
											   i.fk_sales_order_item_status = k.id_sales_order_item_status
											  inner join ims_sales_order l on
											   i.fk_sales_order = l.id_Sales_order
										left join oms_queue_sales_order_item m on
										 i.id_sales_order_item = m.fk_Sales_order_item 
										where  i.fk_sales_order_item_status in('4','15','23','24','25','26','28','30','31','32',
											  '33','34','35','36','40','41','42','43','47','48','49','50','51','52','53',
											  '54','58','67','69','70','71','73')  
										and i.fk_sales_order_item_status Not In ('4','69')
										and l.payment_method not in ('Payment2C2P_OVERTHECOUNTER','SevenEleven','BankTransfer')
										and m.fk_queue not in ('" . $arrArrayfk_queue[$p_CountryId] ."')",
			*/								
			'Pending_0-2_days'		=> "SELECT l.order_nr,l.created_at,l.payment_method,k.name,k.desc  FROM ims_sales_order_item i 
											  inner join ims_sales_order_item_status k on 
											   i.fk_sales_order_item_status = k.id_sales_order_item_status
											  inner join ims_sales_order l on
											   i.fk_sales_order = l.id_Sales_order
										left join oms_queue_sales_order_item m on
										 i.id_sales_order_item = m.fk_Sales_order_item 
										where  i.fk_sales_order_item_status in('4','15','23','24','25','26','28','30','31','32',
											  '33','34','35','36','40','41','42','43','47','48','49','50','51','52','53',
											  '54','58','67','69','70','71','73')  
										and i.fk_sales_order_item_status Not In ('4','69')
										and l.payment_method not in ('Payment2C2P_OVERTHECOUNTER','SevenEleven','BankTransfer')
										and m.fk_queue not in ('" . $arrArrayfk_queue[$p_CountryId] ."')
										and i.created_at > Date_Sub(sysdate(), Interval 2 day);",
			'Pending_2-4_days'		=>"SELECT l.order_nr,l.created_at,l.payment_method,k.name,k.desc  FROM ims_sales_order_item i 
											  inner join ims_sales_order_item_status k on 
											   i.fk_sales_order_item_status = k.id_sales_order_item_status
											  inner join ims_sales_order l on
											   i.fk_sales_order = l.id_Sales_order
										left join oms_queue_sales_order_item m on
										 i.id_sales_order_item = m.fk_Sales_order_item 
										where  i.fk_sales_order_item_status in('4','15','23','24','25','26','28','30','31','32',
											  '33','34','35','36','40','41','42','43','47','48','49','50','51','52','53',
											  '54','58','67','69','70','71','73')  
										and i.fk_sales_order_item_status Not In ('4','69')
										and l.payment_method not in ('Payment2C2P_OVERTHECOUNTER','SevenEleven','BankTransfer')
										and m.fk_queue not in ('" . $arrArrayfk_queue[$p_CountryId] ."')
										and i.created_at between Date_Sub(sysdate(),interval 4 day) 
										and Date_Sub(sysdate(),interval 2 day);",
			'Pending_4-7_days'		=> "SELECT l.order_nr,l.created_at,l.payment_method,k.name,k.desc FROM ims_sales_order_item i 
											  inner join ims_sales_order_item_status k on 
											   i.fk_sales_order_item_status = k.id_sales_order_item_status
											  inner join ims_sales_order l on
											   i.fk_sales_order = l.id_Sales_order
										left join oms_queue_sales_order_item m on
										 i.id_sales_order_item = m.fk_Sales_order_item 
										where  i.fk_sales_order_item_status in('4','15','23','24','25','26','28','30','31','32',
											  '33','34','35','36','40','41','42','43','47','48','49','50','51','52','53',
											  '54','58','67','69','70','71','73')  
										and i.fk_sales_order_item_status Not In ('4','69')
										and l.payment_method not in ('Payment2C2P_OVERTHECOUNTER','SevenEleven','BankTransfer')
										and m.fk_queue not in ('" . $arrArrayfk_queue[$p_CountryId] . "')
						and i.created_at between Date_Sub(sysdate(),interval 7 day) and Date_Sub(sysdate(),interval 4 day);",
						
			'Pending_more_7_days'			=> 	"SELECT l.order_nr,l.created_at,l.payment_method,k.name,k.desc FROM ims_sales_order_item i 
											  inner join ims_sales_order_item_status k on 
											   i.fk_sales_order_item_status = k.id_sales_order_item_status
											  inner join ims_sales_order l on
											   i.fk_sales_order = l.id_Sales_order
										left join oms_queue_sales_order_item m on
										 i.id_sales_order_item = m.fk_Sales_order_item 
										where  i.fk_sales_order_item_status in('4','15','23','24','25','26','28','30','31','32',
											  '33','34','35','36','40','41','42','43','47','48','49','50','51','52','53',
											  '54','58','67','69','70','71','73')  
										and i.fk_sales_order_item_status Not In ('4','69')
										and l.payment_method not in ('Payment2C2P_OVERTHECOUNTER','SevenEleven','BankTransfer')
										and m.fk_queue not in ('" . $arrArrayfk_queue[$p_CountryId] ."')
								and i.created_at < Date_Sub(sysdate(),interval 7 day) ;"
			);
		
		$arrData = array();
		if(isset($arrItemQuery[$idQuery]))
		{			
			$arrData = $this->_db->fetchAll($arrItemQuery[$idQuery]); 	            
		}
		return $arrData;
	}
}
?>